package com.ylbh.modules.accessory;

import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.ylbh.common.exception.NormalException;
import com.ylbh.common.pojo.business.vo.GetUserInfoVO;
import com.ylbh.common.pojo.goods.dto.GoodsDTO;
import com.ylbh.common.utils.CommonResult;
import com.ylbh.common.utils.FastJsonUtil;
import com.ylbh.common.utils.HttpClientHelperUtils;
import com.ylbh.common.utils.Ref;
import com.ylbh.modules.business.service.ShoppingUserService;
import com.ylbh.modules.goods.service.ShoppingGoodsService;
import net.sf.json.JSONArray;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;


/**
 * @author: czx
 * @date: 2019/09/24 10:04
 */
@RequestMapping("test")
@RestController
public class ExcelController {

    private final Logger log = LoggerFactory.getLogger(ExcelController.class);

    private static final String YlbhId = "33307";

    @Autowired
    private ShoppingGoodsService shoppingGoodsService;
    @Autowired
    private ShoppingUserService shoppingUserService;

    @RequestMapping("excel")
    public void exportExcel(HttpServletResponse response, HttpServletRequest request) throws Exception {


//        List<GoodsDTO> list = shoppingGoodsService.getAllGoods();
        List<GoodsDTO> list = shoppingGoodsService.queryGoodsByTime();
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
        SXSSFSheet sheet = sxssfWorkbook.createSheet("商品");
//        CellRangeAddress region1 = new CellRangeAddress(0, 0, 7, 18);
//        sheet.addMergedRegion(region1);
//        SXSSFRow headTitle = sheet.createRow(0);
//        headTitle.createCell(0).setCellValue("商品信息表");

        SXSSFRow headRow = sheet.createRow(0);

//        headRow.createCell(0).setCellValue("ID");
//        headRow.createCell(1).setCellValue("商品名");
//        headRow.createCell(2).setCellValue("商品类型");
//        headRow.createCell(3).setCellValue("型号");
//        headRow.createCell(4).setCellValue("价格");
//        headRow.createCell(5).setCellValue("赠送积分");
//        headRow.createCell(6).setCellValue("兑换积分");
//        headRow.createCell(7).setCellValue("进价");
//        headRow.createCell(8).setCellValue("规格ID");
//        headRow.createCell(9).setCellValue("商城售价");
//        headRow.createCell(10).setCellValue("赠送积分");
//        headRow.createCell(11).setCellValue("兑换积分");
//        headRow.createCell(12).setCellValue("进价");
//        headRow.createCell(13).setCellValue("规格图");
//        headRow.createCell(14).setCellValue("库存");
//        headRow.createCell(15).setCellValue("原价");
//        headRow.createCell(16).setCellValue("类型");
//        headRow.createCell(17).setCellValue("利润");
//        headRow.createCell(18).setCellValue("成本价");
//        headRow.createCell(19).setCellValue("规格名");

        headRow.createCell(0).setCellValue("ID");
        headRow.createCell(1).setCellValue("商品名");
        headRow.createCell(2).setCellValue("型号");
        headRow.createCell(3).setCellValue("规格ID");
        headRow.createCell(4).setCellValue("商城售价");
        headRow.createCell(5).setCellValue("赠送积分");
        headRow.createCell(6).setCellValue("兑换积分");
        headRow.createCell(7).setCellValue("进价");
        headRow.createCell(8).setCellValue("规格图");
        headRow.createCell(9).setCellValue("库存");
        headRow.createCell(10).setCellValue("原价");
        headRow.createCell(11).setCellValue("类型");
        headRow.createCell(12).setCellValue("利润");
        headRow.createCell(13).setCellValue("成本价");
        headRow.createCell(14).setCellValue("保质期开始时间");
        headRow.createCell(15).setCellValue("保质期结束时间");
        headRow.createCell(16).setCellValue("重量");
        headRow.createCell(17).setCellValue("规格名");

        int count = 1;
        int a = 0;
        for (GoodsDTO dto : list) {
            List<GoodsInventoryDetailsToJsons> toList = FastJsonUtil.toList(dto.getGoodsInventoryDetail(), GoodsInventoryDetailsToJsons.class);
            if (toList.size() > 1) {
//                CellRangeAddress region1 = new CellRangeAddress(count, count + toList.size() - 1, 0, 0);
//                CellRangeAddress region2 = new CellRangeAddress(count, count + toList.size() - 1, 1, 1);
//                CellRangeAddress region3 = new CellRangeAddress(count, count + toList.size() - 1, 2, 2);
//                CellRangeAddress region4 = new CellRangeAddress(count, count + toList.size() - 1, 3, 3);
//                CellRangeAddress region5 = new CellRangeAddress(count, count + toList.size() - 1, 4, 4);
//                CellRangeAddress region6 = new CellRangeAddress(count, count + toList.size() - 1, 5, 5);
//                CellRangeAddress region7 = new CellRangeAddress(count, count + toList.size() - 1, 6, 6);
//                CellRangeAddress region8 = new CellRangeAddress(count, count + toList.size() - 1, 7, 7);
//                count = count + toList.size();
//                sheet.addMergedRegion(region1);
//                sheet.addMergedRegion(region2);
//                sheet.addMergedRegion(region3);
//                sheet.addMergedRegion(region4);
//                sheet.addMergedRegion(region5);
//                sheet.addMergedRegion(region6);
//                sheet.addMergedRegion(region7);
//                sheet.addMergedRegion(region8);

                CellRangeAddress region1 = new CellRangeAddress(count, count + toList.size() - 1, 0, 0);
                CellRangeAddress region2 = new CellRangeAddress(count, count + toList.size() - 1, 1, 1);
                CellRangeAddress region3 = new CellRangeAddress(count, count + toList.size() - 1, 2, 2);
                count = count + toList.size();
                sheet.addMergedRegion(region1);
                sheet.addMergedRegion(region2);
                sheet.addMergedRegion(region3);

            } else {
                count = count + toList.size();
            }
            SXSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            dataRow.createCell(0).setCellValue(dto.getId());
            dataRow.createCell(1).setCellValue(dto.getGoodsName());
            dataRow.createCell(2).setCellValue(dto.getGoodsSerial());
//            dataRow.createCell(3).setCellValue(dto.getGoodsSerial());
//            dataRow.createCell(4).setCellValue(dto.getVipPrice().toString());
//            dataRow.createCell(5).setCellValue(dto.getIntegral());
//            if (null != dto.getGiveIntegral())
//                dataRow.createCell(6).setCellValue(dto.getGiveIntegral());
//            else
//                dataRow.createCell(6).setCellValue("");
//            if (null != dto.getBuyerPrice())
//                dataRow.createCell(7).setCellValue(dto.getBuyerPrice().toString());
//            else
//                dataRow.createCell(7).setCellValue("");
            List<GoodsInventoryDetailsToJsons> toJsonList = FastJsonUtil.toList(dto.getGoodsInventoryDetail(), GoodsInventoryDetailsToJsons.class);

            for (int i = 0; i < toJsonList.size(); i++) {
                if (i > 0) {
                    dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
                }
//                dataRow.createCell(8).setCellValue(toJsonList.get(i).getId());
//                dataRow.createCell(9).setCellValue(toJsonList.get(i).getVipPrice());
//                if (null != toJsonList.get(i).getGiveIntegral())
//                    dataRow.createCell(10).setCellValue(toJsonList.get(i).getGiveIntegral());
//                else
//                    dataRow.createCell(10).setCellValue("");
//                dataRow.createCell(11).setCellValue(toJsonList.get(i).getIntegral());
//                if (null != toJsonList.get(i).getBuyerPrice())
//                    dataRow.createCell(12).setCellValue(toJsonList.get(i).getBuyerPrice());
//                else
//                    dataRow.createCell(12).setCellValue("");
//                dataRow.createCell(13).setCellValue(toJsonList.get(i).getImgPath());
//                dataRow.createCell(14).setCellValue(toJsonList.get(i).getCount());
//                dataRow.createCell(15).setCellValue(toJsonList.get(i).getPrice());
//                dataRow.createCell(16).setCellValue(toJsonList.get(i).getPriceType());
//                dataRow.createCell(17).setCellValue(toJsonList.get(i).getGoodsCostingPrice());
//                dataRow.createCell(18).setCellValue(toJsonList.get(i).getShowCostingPrice());

                dataRow.createCell(3).setCellValue(toJsonList.get(i).getId());
                dataRow.createCell(4).setCellValue(toJsonList.get(i).getVipPrice());
                if (null != toJsonList.get(i).getGiveIntegral())
                    dataRow.createCell(5).setCellValue(toJsonList.get(i).getGiveIntegral());
                else
                    dataRow.createCell(5).setCellValue("");
                dataRow.createCell(6).setCellValue(toJsonList.get(i).getIntegral());
                if (null != toJsonList.get(i).getBuyerPrice())
                    dataRow.createCell(7).setCellValue(toJsonList.get(i).getBuyerPrice());
                else
                    dataRow.createCell(7).setCellValue("");
                dataRow.createCell(8).setCellValue(toJsonList.get(i).getImgPath());
                dataRow.createCell(9).setCellValue(toJsonList.get(i).getCount());
                dataRow.createCell(10).setCellValue(toJsonList.get(i).getPrice());
                dataRow.createCell(11).setCellValue(toJsonList.get(i).getPriceType());
                dataRow.createCell(12).setCellValue(toJsonList.get(i).getGoodsCostingPrice());
                dataRow.createCell(13).setCellValue(toJsonList.get(i).getShowCostingPrice());
                dataRow.createCell(14).setCellValue(toJsonList.get(i).getStartExpDate());
                dataRow.createCell(15).setCellValue(toJsonList.get(i).getEndExpDate());
                dataRow.createCell(16).setCellValue(toJsonList.get(i).getWeight());

                String id = "";
                if (!StringUtils.isEmpty(toJsonList.get(i).getId())) {
                    String[] ids = toJsonList.get(i).getId().split("_");
                    List<String> specValueById = shoppingGoodsService.getSpecValueById(Arrays.asList(ids));
                    for (String v:specValueById) {
                        id = id + v + ";";
                    }
                }
                dataRow.createCell(17).setCellValue(id);
            }
            System.out.println("当前数量：" + a);
            a = a + 1;
        }
        // 下载导出
        String filename = "送贝商城商品信息表";
        // 设置头信息
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        //一定要设置成xlsx格式
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename + ".xls", "UTF-8"));
        //创建一个输出流
        ServletOutputStream outputStream = response.getOutputStream();
        //写入数据
        sxssfWorkbook.write(outputStream);

        // 关闭
        outputStream.close();
        sxssfWorkbook.close();
    }

    @RequestMapping("importExcel")
    public void importExcel(@RequestParam(value = "file")MultipartFile file) throws Exception, NormalException {
        List<Object[]> objects = readExcelToObj(file.getInputStream(), 0);
        int id = 0;
        String goodsName = "";
        int priceType = 0;
        String goodsSerial = "";
        BigDecimal vipPrice = null;
        int giveIntegral = 0;
        int integral = 0;
        BigDecimal buyerPrice = null;
        ArrayList<CombinedCommodities> list = new ArrayList<>();

        ArrayList<GoodsInventoryDetailsToJsons> list1 = new ArrayList<>();

        if (objects.size() > 0) {
            for (int i = 1; i < objects.size(); i++) {
                Object[] obj = objects.get(i);
                if (null != obj[0] && !"".equals(obj[0]) && id != Integer.parseInt(obj[0].toString().replace(".0",""))) {
                    if (i > 1) {
                        CombinedCommodities com = new CombinedCommodities();
                        com.setId(id);
                        com.setGoodsName(goodsName);
//                        com.setPriceType(priceType);
                        com.setGoodsSerial(goodsSerial);
//                        com.setVipPrice(vipPrice);
//                        com.setGiveIntegral(giveIntegral);
//                        com.setIntegral(integral);
//                        com.setBuyerPrice(buyerPrice);
//                        String s = FastJsonUtil.toJSONString(list1);
                        JSONArray jsonArray = JSONArray.fromObject(list1);
                        com.setGoodsInventoryDetail(jsonArray.toString());
                        list.add(com);
                        list1.clear();
                    }

                    id = 0;
                    goodsName = "";
                    priceType = 0;
                    goodsSerial = "";
                    vipPrice = null;
                    giveIntegral = 0;
                    integral = 0;
                    buyerPrice = null;

                    id = Integer.parseInt(obj[0].toString().replace(".0",""));
                    goodsName = obj[1].toString();
//                    priceType = Integer.parseInt(obj[2].toString().replace(".0", ""));
                    goodsSerial = obj[2].toString();
//                    vipPrice = new BigDecimal(obj[4].toString());
//                    giveIntegral = Integer.parseInt(obj[5].toString().replace(".0",""));
//                    if (!StringUtils.isEmpty(obj[6].toString()))
//                        integral = Integer.parseInt(obj[6].toString().replace(".0",""));
//                    else
//                        integral = 0;
//                    if (!StringUtils.isEmpty(obj[12].toString()))
//                        buyerPrice = new BigDecimal(obj[12].toString());
//                    else
//                        buyerPrice = null;

                    GoodsInventoryDetailsToJsons gidtj = new GoodsInventoryDetailsToJsons();
                    gidtj.setImgPath(obj[8].toString());
                    gidtj.setGoodsCostingPrice(obj[12].toString());
                    gidtj.setIntegral(Integer.parseInt(obj[6].toString().replace(".0", "")));
                    gidtj.setCount(Integer.parseInt(obj[9].toString().replace(".0", "")));
                    gidtj.setPrice(obj[10].toString());
                    gidtj.setVipPrice(obj[4].toString());
                    gidtj.setPriceType(obj[11].toString());
                    gidtj.setBuyerPrice(obj[7].toString());
                    if (!StringUtils.isEmpty(obj[5].toString()))
                        gidtj.setGiveIntegral(Integer.parseInt(obj[5].toString().replace(".0", "")));
                    else
                        gidtj.setGiveIntegral(0);
                    gidtj.setId(obj[3].toString());
                    System.out.println("条数："+i + "；" + obj[13].toString());
                    System.out.println(new BigDecimal(obj[13]+ "")+"");
                    gidtj.setShowCostingPrice(new BigDecimal(obj[13] + "").toString());
                    gidtj.setStartExpDate(obj[14].toString());
                    gidtj.setEndExpDate(obj[15].toString());
                    gidtj.setWeight(obj[16] +"");
                    list1.add(gidtj);
                } else {
                    // 表示是上一个商品
                    GoodsInventoryDetailsToJsons gidtj = new GoodsInventoryDetailsToJsons();
//                    gidtj.setImgPath(obj[13].toString());
//                    gidtj.setGoodsCostingPrice(obj[17].toString());
//                    gidtj.setIntegral(Integer.parseInt(obj[11].toString().replace(".0", "")));
//                    gidtj.setCount(Integer.parseInt(obj[14].toString().replace(".0", "")));
//                    gidtj.setPrice(obj[15].toString());
//                    gidtj.setVipPrice(obj[9].toString());
//                    gidtj.setPriceType(obj[16].toString());
//                    gidtj.setBuyerPrice(obj[12].toString());
//                    gidtj.setGiveIntegral(Integer.parseInt(obj[10].toString().replace(".0", "")));
//                    gidtj.setId(obj[8].toString());
//
//                    gidtj.setShowCostingPrice(new BigDecimal(obj[17]+ "").multiply(new BigDecimal("0.7")).intValue() + "");
                    gidtj.setImgPath(obj[8].toString());
                    gidtj.setGoodsCostingPrice(obj[12].toString());
                    gidtj.setCount(Integer.parseInt(obj[9].toString().replace(".0", "")));
                    gidtj.setIntegral(Integer.parseInt(obj[6].toString().replace(".0", "")));
                    gidtj.setPrice(obj[10].toString());
                    gidtj.setVipPrice(obj[4].toString());
                    gidtj.setPriceType(obj[11].toString());
//                    gidtj.setGiveIntegral(Integer.parseInt(obj[5].toString().replace(".0", "")));
                    if (!StringUtils.isEmpty(obj[5].toString()))
                        gidtj.setGiveIntegral(Integer.parseInt(obj[5].toString().replace(".0", "")));
                    else
                        gidtj.setGiveIntegral(0);
                    gidtj.setBuyerPrice(obj[7].toString());
                    gidtj.setId(obj[3].toString());
                    gidtj.setShowCostingPrice(new BigDecimal(obj[13] + "").toString());
                    gidtj.setEndExpDate(obj[15].toString());
                    gidtj.setStartExpDate(obj[14].toString());
                    gidtj.setWeight(obj[16] +"");
                    list1.add(gidtj);
                }
            }
        }
        CombinedCommodities com = new CombinedCommodities();
        com.setId(id);
        com.setGoodsSerial(goodsSerial);
        com.setGoodsName(goodsName);
//        com.setPriceType(priceType);
//        com.setVipPrice(vipPrice);
//        com.setGiveIntegral(giveIntegral);
//        com.setIntegral(integral);
//        com.setBuyerPrice(buyerPrice);
//        String s = FastJsonUtil.toJSONString(list1);
        JSONArray jsonDto = JSONArray.fromObject(list1);
        com.setGoodsInventoryDetail(jsonDto.toString());
        list.add(com);

//        for (int i = 1; i < objects.size(); i++) {
//            Object[] o = objects.get(i);
//            if (!"".equals(o[0])) {
//                id = o[0].toString().trim();
//                goodsName = o[1].toString().trim();
//                priceType = o[2].toString().trim();
//                goodsSerial = o[3].toString().trim();
//                vipPrice = o[4].toString().trim();
//                giveIntegral = o[5].toString().trim();
//                integral = o[6].toString().trim();
//                buyerPrice = o[7].toString().trim();
//            } else {
//                o[0] = id;
//                o[1] = goodsName;
//                o[2] = priceType;
//                o[3] = goodsSerial;
//                o[4] = vipPrice;
//                o[5] = giveIntegral;
//                o[6] = integral;
//                o[7] = buyerPrice;
//            }
//        }
//        CombinedCommodities commodities = new CombinedCommodities();
//        for (int i = 1; i < objects.size(); i++) {
//            if (null != commodities.getId() && !"".equals(commodities.getId())) {
//                if (commodities.equals(objects.get(i)[0])) {
//                    GoodsInventoryDetailsToJsons details = new GoodsInventoryDetailsToJsons();
//                    details.setImgPath(objects.get(i)[13].toString());
//                    details.setGoodsCostingPrice(objects.get(i)[17].toString());
//                    details.setIntegral(objects.get(i)[11].toString());
//                    details.setCount(objects.get(i)[14].toString());
//                    details.setPrice(objects.get(i)[15].toString());
//                    details.setVipPrice(objects.get(i)[9].toString());
//                    details.setPriceType(objects.get(i)[16].toString());
//                    details.setBuyerPrice(objects.get(i)[12].toString());
//                    details.setGiveIntegral(objects.get(i)[10].toString());
//                    details.setId(objects.get(i)[8].toString());
//                    details.setShowCostingPrice(objects.get(i)[18].toString());
//                } else {
//
//                }
//            } else {
//                commodities.setId(Integer.parseInt(objects.get(i)[0].toString()));
//                commodities.setGoodsName(objects.get(i)[1].toString());
//                commodities.setPriceType(Integer.parseInt(objects.get(i)[2].toString()));
//                commodities.setGoodsSerial(objects.get(i)[3].toString());
//                commodities.setVipPrice(new BigDecimal(objects.get(i)[4].toString()));
//                commodities.setGiveIntegral(Integer.parseInt(objects.get(i)[5].toString()));
//                commodities.setIntegral(Integer.parseInt(objects.get(i)[6].toString()));
//                commodities.setBuyerPrice(new BigDecimal(objects.get(i)[7].toString()));
//            }
//        }
        for (int i = 0; i < list.size(); i++) {
           shoppingGoodsService.modifyGoodsWeight(list.get(i).getId(), list.get(i).getGoodsInventoryDetail());
        }
        System.out.println(objects);
    }

    public static void main(String[] args) {
        int i = new BigDecimal("15").multiply(new BigDecimal("0.7")).intValue();
        System.out.println(i);

    }

    public static List<Object[]> readExcelToObj(InputStream is, int indexNum) throws Exception {
        Workbook wb = null;
        List<Object[]> list = null;
        try{
            list = new ArrayList<>();
            wb = WorkbookFactory.create(is);
            readExcel(wb, 0, 0, 0, list, indexNum);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine, List<Object[]> list, int indexNum) {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        for (int i = startReadLine; i <sheet.getLastRowNum() - tailLine + 1; i++) {
            row = sheet.getRow(i);
            List<Object> objList = new ArrayList<>();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell c = row.getCell(j);
                if (c == null) {
                    objList.add("");
                    continue;
                }
                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                if (isMerge) {
                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
                    objList.add(rs);
                } else {
                    objList.add(getCellValue(c));
                }
            }
            while (objList.size() < indexNum) {
                objList.add("");
            }
            list.add(objList.toArray());
        }
    }

    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    public static String getCellValue(Cell cell) {
        DecimalFormat df = new DecimalFormat("#");
        if (cell == null)
            return "";
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(df.format(cell.getNumericCellValue()));
        }
        return "";
    }

    public static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @RequestMapping("importExcel2")
    public CommonResult importStaffExcel(@RequestParam(value = "file")MultipartFile file) throws Exception {
        List<Object[]> objects = readExcelToObj(file.getInputStream(), 0);
        if (objects.size() > 0) {
            Object[] ob = objects.get(1);
            if (!"序号".equals(ob[0].toString()) || !"姓名".equals(ob[1].toString()) && !"积分".equals(ob[2].toString()) && !"充值账号".equals(ob[3].toString()) && !"备注".equals(ob[4].toString())) {
                return Ref.error("模板有误，请参照模板excel文件");
            }
            for (int i = 1; i < objects.size(); i++) {
                Object[] obj = objects.get(i);
                if (i > 1) {
                    GetUserInfoVO userVo = shoppingUserService.getUserInfoByUserName(obj[3].toString());
                    if (obj[2].toString().indexOf(" ") != -1 || obj[2].toString() == "") {
                        return Ref.error("用户：" + obj[1].toString() + "充值积分有误或不存在");
                    }
                    if (obj[4].toString() == null || userVo== null ) {
                        return Ref.error("用户：" + obj[1].toString() + "充值账号有误或不存在");
                    }
                }
            }
            for (int i = 1; i < objects.size(); i++) {
                Object[] obj = objects.get(i);
                if (null != obj[0] && !"".equals(obj[0])) {
                    if (i > 1) {
                        GetUserInfoVO userVo = shoppingUserService.getUserInfoByUserName(obj[3].toString());
                        userVo.setRemark(obj[4].toString());
                        boolean bl = shoppingUserService.updateIntegral(obj[3].toString(), YlbhId, Integer.parseInt(obj[2].toString() == "" ? "0" : obj[2].toString().replace(".0", "")));
                        if (bl) {
                            shoppingUserService.updateLog(userVo, YlbhId, Integer.parseInt(obj[2].toString() == "" ? "0" : obj[2].toString().replace(".0", "")));
                        }
                    }
                }
            }
        }
        return Ref.ok();
    }
}
